package com.Servlet;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.util.CellRangeAddress;

import com.jdbc.Jdbc;
import com.project.bean.Sales;

/**
 * Servlet implementation class salesOutput
 */
@WebServlet("/salesOutput")
public class salesOutput extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public salesOutput() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		response.getWriter().append("Served at: ").append(request.getContextPath());
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("utf-8");
		String username = (String) request.getSession().getAttribute("username");
		String role = (String) request.getSession().getAttribute("role");
		String find = null;
		response.setContentType("application/octet-stream");
		response.setCharacterEncoding("UTF-8");
		// 设置文件的名字
		response.addHeader("Content-Disposition", "attachment; filename=salesinfo.xls");
		String headline = "销售信息";
		String[] title = { "学生编号", "中文名", "英文名", "性别", "出生日期", "预约记录", "是否到访", "信息来源", "市场推广员", "课程顾问", "电话课程顾问",
				"分配状态", "分配时间", "联系电话1","联系电话2","备注","录入人员" };
		// 创建EXCEL工作簿
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 创建一个工作sheet
		HSSFSheet sheet = workbook.createSheet();
		CellStyle cellStyle = workbook.createCellStyle();
		DataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat("@"));// 默认为文本格式
		// 创建第一行
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = null;
		// 创建标题
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
		cell = row.createCell(0);
		cell.setCellValue(headline);
		// 创建第二行
		HSSFRow rowtitle = sheet.createRow(1);
		// 插入第二行title
		for (int i = 0; i < title.length; i++) {
			sheet.setDefaultColumnStyle(i, cellStyle);
			sheet.setColumnWidth(i, 20* 256);//设置好cell的宽度
			cell = rowtitle.createCell(i);
			cell.setCellValue(title[i]);
		}
		if (role.equals("管理员")) {
			find = "select*from sales ";
		} else {
			find = "select*from sales where username='" + username + "'";
		} // 追加数据,1先从数据库中获取结果集并且放到LIST中，然后一行一行地放入

		Jdbc j = new Jdbc();
		ResultSet rs;
		List<Sales> list = new ArrayList<>();

		try {
			j.Jdbc_Q2(find);
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		rs = j.rs;
		try {
			while (rs.next()) {
				Sales s = new Sales();
				s.setId(rs.getString("id"));
				s.setChinesename(rs.getString("chinesename"));
				s.setEnglishname(rs.getString("englishname"));
				s.setSex(rs.getString("sex"));
				s.setBirthday(rs.getString("birthday"));
				s.setReservation(rs.getString("reservation"));
				s.setAttend(rs.getString("attend"));
				s.setMessagefrom(rs.getString("messagefrom"));
				s.setMarketman(rs.getString("marketman"));
				s.setCounselor(rs.getString("counselor"));
				s.setPhonecounselor(rs.getString("phonecounselor"));
				s.setDistributionstate(rs.getString("distributionstate"));
				s.setDistributiontime(rs.getString("distributiontime"));
				s.setPhone1(rs.getString("phone1"));
				s.setPhone2(rs.getString("phone2"));
				s.setRemarks(rs.getString("remarks"));
				s.setUsername(rs.getString("username"));

				list.add(s);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		System.out.println(list.size());

		int i = 2;

		for (Sales s : list) {
			HSSFRow nextrow = sheet.createRow(i);
			HSSFCell cell2 = nextrow.createCell(0);
			cell2.setCellValue(s.getId());
			cell2 = nextrow.createCell(1);
			cell2.setCellValue(s.getChinesename());
			cell2 = nextrow.createCell(2);
			cell2.setCellValue(s.getEnglishname());
			cell2 = nextrow.createCell(3);
			cell2.setCellValue(s.getSex());
			cell2 = nextrow.createCell(4);
			cell2.setCellValue(s.getBirthday());
			cell2 = nextrow.createCell(5);
			cell2.setCellValue(s.getReservation());
			cell2 = nextrow.createCell(6);
			cell2.setCellValue(s.getAttend());
			cell2 = nextrow.createCell(7);
			cell2.setCellValue(s.getMessagefrom());
			cell2 = nextrow.createCell(8);
			cell2.setCellValue(s.getMarketman());
			cell2 = nextrow.createCell(9);
			cell2.setCellValue(s.getCounselor());
			cell2 = nextrow.createCell(10);
			cell2.setCellValue(s.getPhonecounselor());
			cell2 = nextrow.createCell(11);
			cell2.setCellValue(s.getDistributionstate());
			cell2 = nextrow.createCell(12);
			cell2.setCellValue(s.getDistributiontime());
			cell2 = nextrow.createCell(13);
			cell2.setCellValue(s.getPhone1());
			cell2 = nextrow.createCell(14);
			cell2.setCellValue(s.getPhone2());
			cell2 = nextrow.createCell(15);
			cell2.setCellValue(s.getRemarks());
			cell2 = nextrow.createCell(16);
			cell2.setCellValue(s.getUsername());
			i++;
		}
		workbook.write(response.getOutputStream());
		workbook.close();
		// 创建一个文件

	}

}
